package com.oocl.ita.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.ita.po.Person;
import com.oocl.ita.util.DBUtil;

public class PersonDaoImpl implements PersonDao {

	public int addPerson(Person p) {
		String sql = "insert into person(id, name, birth, telphone, deptNo, salary) values (person_seq.nextval,?,?,?,?,?)";
		Connection connection = null;
		PreparedStatement statement = null;
		int b = 0;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			statement.setString(1, p.getName());
			statement.setString(2, p.getBirth());
			statement.setString(3, p.getTelphone());
			statement.setInt(4, p.getDeptno());
			statement.setFloat(5, p.getSalary());
			b = statement.executeUpdate();
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
					b = -1;
				} catch (SQLException e1) {
					b = -2;
				}
			}else{
				b = -3;
			}
		} finally {
			DBUtil.free(connection, statement, null);
		}
		return b;
	}

	public int deletePerson(int id) {
		String sql = "delete from person where id=?";
		Connection connection = null;
		PreparedStatement statement = null;
		int b = 0;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			b = statement.executeUpdate();
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
					b = -1;
				} catch (SQLException e1) {
					b = -2;
				}
			}else{
				b = -3;
			}
		} finally {
			DBUtil.free(connection, statement, null);
		}

		return b;
	}

	public int updatePerson(Person p) {
		String sql = "update person set name=?,birth=?,telphone=?,deptno=?,salary=? where id=?";		
		Connection connection = null;
		PreparedStatement statement = null;
		int b = 0; 
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			statement.setString(1, p.getName());
			statement.setString(2, p.getBirth());
			statement.setString(3, p.getTelphone());
			statement.setInt(4, p.getDeptno());
			statement.setFloat(5, p.getSalary());
			statement.setInt(6, p.getId());
			b = statement.executeUpdate();
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
					b = -1;
				} catch (SQLException e1) {
					b = -2;
				}
			}else{
				b = -3;
			}
		} finally {
			DBUtil.free(connection, statement, null);
		}
		return b;
	}

	public List<Person> showAllPerson() {
		String sql = "select * from person";
		Connection connection = null;
		PreparedStatement statement = null;
		List<Person> personsList = new ArrayList<Person>();
		ResultSet resultSet = null;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while(resultSet.next()){
				int id = resultSet.getInt(1);
				String name = resultSet.getString(2);
				String birth = resultSet.getString(3);
				String telphone = resultSet.getString(4);
				int deptNo = resultSet.getInt(5);
				float salary = resultSet.getFloat(6);
				Person person = new Person(name, birth, telphone, deptNo, salary);
				person.setId(id);
				personsList.add(person);
			}
		} catch (SQLException e) {
			System.out.println("select error");
		}finally {
			DBUtil.free(connection, statement, resultSet);
		}
		return personsList;
	}

	public Person loadPerson(int id) {
		String sql = "select * from person where id=?";
		Connection connection = null;
		PreparedStatement statement = null;
		Person person = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				String name = resultSet.getString(2);
				String birth = resultSet.getString(3);
				String telphone = resultSet.getString(4);
				int deptNo = resultSet.getInt(5);
				float salary = resultSet.getFloat(6);
				person = new Person(name, birth, telphone, deptNo, salary);
				person.setId(id);
			}
		} catch (SQLException e) {
			System.out.println("select error");
		}finally {
			DBUtil.free(connection, statement, resultSet);
		}
		return person;
	}

	public List<Person> showPersonGroup(int deptno) {
		String sql = "select * from person join department on person.deptno=department.id where deptno=?";
		Connection connection = null;
		PreparedStatement statement = null;
		List<Person> persons = new ArrayList<Person>();
		ResultSet resultSet = null;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			statement.setInt(1, deptno);
			resultSet = statement.executeQuery();
			while(resultSet.next()){
				String name = resultSet.getString(2);
				String birth = resultSet.getString(3);
				String telphone = resultSet.getString(4);
				int deptNo = resultSet.getInt(5);
				float salary = resultSet.getFloat(6);
				Person person = new Person(name, birth, telphone, deptNo, salary);
				person.setId(resultSet.getInt(1));
				persons.add(person);
			}
		} catch (SQLException e) {
			persons = null;
			System.out.println("select error");
		}finally {
			DBUtil.free(connection, statement, resultSet);
		}
		return persons;
	}

}
